package com.gem.controller;

import com.alibaba.druid.util.StringUtils;
import com.gem.domain.CDailySpending;
import com.gem.domain.User;
import com.gem.service.CDailySpendingService;
import com.gem.service.UserService;
import com.gem.util.ExcelUtil;
import com.gem.util.JSONResultUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.BufferedInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.*;

/**
 * @author Apolar_GuanYH-管阳红
 * Description: 日常经费Excel控制器
 * create 2019/11/3 15:55
 * Modified By:
 */
@Controller
@SuppressWarnings("all")
@RequestMapping("/ds")
public class CDailySpendingExcelController {

    //自动注入JSONResultUtil
    private JSONResultUtil result;

    @Autowired
    public void setResult(JSONResultUtil result) {
        this.result = result;
    }

    //自动注入CDailySpendingService
    private CDailySpendingService cDailySpendingService;

    @Autowired
    public void setcDailySpendingService(CDailySpendingService cDailySpendingService) {
        this.cDailySpendingService = cDailySpendingService;
    }

    //自动注入UserService
    private UserService userService;

    @Autowired
    public void setUserService(UserService userService) {
        this.userService = userService;
    }

    //导出excel模板
    @RequestMapping("/exportTemplate")
    public void exportExcelTemplate(HttpServletResponse response, HttpSession session) {
        try {
            //创建excel工作簿对象//一个表格文件！
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet对象//一个表单！
            HSSFSheet sheet = workbook.createSheet("日常经费");

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            //模板第一行例子展示
            HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateRedStyle(workbook);

            //创建行对象
            HSSFRow row = null;

            // 将第一行的三个单元格给合并,模板固定
            //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
            sheet = getRows(sheet, cellStyle, cellStyle2);
            //在第四行给一行数据模板,根据对应表的对应字段填写
            HSSFCell cell;
            row = sheet.createRow(3);
            Map<String, Object> map = new LinkedHashMap<>();
            map.put("Date", new Date());
            map.put("Double1", 100.0);
            map.put("Double2", 100.0);
            map.put("Double3", 200.0);
            map.put("String1", "张三，李四，王五");
            map.put("Integer", 1);
            map.put("Double4", 100.0);
            map.put("Double5", 100.0);
            map.put("Double6", 100.0);
            map.put("Double7", 100.0);
            map.put("Double8", 100.0);
            map.put("String2", "其它的费用信息");
            map.put("String3", "李四");
            map.put("String4", "备注信息");
            row = ExcelUtil.setExcelExample(row, map);
            row.getCell(0).setCellStyle(cellStyleDate);
            for (int i = 1; i < 14; i++) {
                row.getCell(i).setCellStyle(cellStyle3);

            }
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=DailySpendingTemplate.xls");
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //导出excel数据
    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response, HttpSession session) {
        try {
            //创建excel工作簿对象//一个表格文件！
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建sheet对象//一个表单！
            HSSFSheet sheet = workbook.createSheet("日常经费");

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            //模板第一行例子展示
            HSSFCellStyle cellStyle3 = ExcelUtil.getExamplStyle(workbook);

            //数据
            HSSFCellStyle cellStyle4 = ExcelUtil.getCenterStyle(workbook);

            // 单元格类型日期格式化模板
            HSSFCellStyle cellStyleDate1 = ExcelUtil.getDateRedStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate2 = ExcelUtil.getDateStyle(workbook);

            //创建行对象
            HSSFRow row = null;

            // 将第一行的三个单元格给合并,模板固定
            //注意 java的参数是从0开始 即表示excel中的一行或者第一列  //Row行 Col列
            sheet = getRowsData(sheet, cellStyle, cellStyle2);
            //在第四行给一行数据模板,根据对应表的对应字段填写
            HSSFCell cell;
            User user = (User) session.getAttribute("user");
            List<CDailySpending> cDailySpendingList;
            if (user.getAuthority() == 1 && "市场".equals(user.getDepartment())) {
                cDailySpendingList = cDailySpendingService.selectDSForMarketService();
            } else if (user.getAuthority() == 1 && "财务".equals(user.getDepartment())) {
                cDailySpendingList = cDailySpendingService.selectAllDS();
            } else {
                cDailySpendingList = cDailySpendingService.selectDSByUidService(user.getUid());
            }
            CDailySpending cDailySpending;
            for (int i = 0; i < cDailySpendingList.size(); i++) {
                cDailySpending = cDailySpendingList.get(i);
                row = sheet.createRow(2 + i);
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("Date", cDailySpending.getDsDate());
                map.put("Double1", cDailySpending.getDsTrafficTaxi());
                map.put("Double2", cDailySpending.getDsTrafficOther());
                map.put("Double3", cDailySpending.getDsTrafficTotal());
                map.put("Double4", cDailySpending.getDsStayTotalprice());
                map.put("String1", cDailySpending.getDsStayPeople());
                map.put("Integer", cDailySpending.getDsStayCount());
                map.put("Double5", cDailySpending.getDsStayUnitprice());
                map.put("Double6", cDailySpending.getDsEntertainStu());
                map.put("Double7", cDailySpending.getDsEntertainLeader());
                map.put("Double8", cDailySpending.getDsServices());
                map.put("Double9", cDailySpending.getDsOther());
                map.put("String2", cDailySpending.getDsOtherState());
                map.put("String3", cDailySpending.getDsSeller());
                map.put("Double10", cDailySpending.getDsTotal());
                map.put("String4", cDailySpending.getDsRemark());
                row = ExcelUtil.setExcelExample(row, map);
                row.getCell(0).setCellStyle(cellStyleDate2);
                for (int j = 1; j < 16; j++) {
                    row.getCell(j).setCellStyle(cellStyle4);
                }
            }
            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=DailySpending.xls");
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //上传excel
    @RequestMapping(value = "/dsUploadExcel", method = RequestMethod.POST)
    @ResponseBody
    public JSONResultUtil dsUploadExcel(@RequestParam(value = "file", required = false) MultipartFile file) {
        try {
            InputStream inputStream = null;
            inputStream = file.getInputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(inputStream);
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();
            CDailySpending cDailySpending = null;
            HSSFRow row = null;
            //读取数据
            List<CDailySpending> list = new ArrayList<>();
            for (int i = 3; i <= lastRowNum; i++) {
                cDailySpending = new CDailySpending();
                row = sheet.getRow(i);
                if (row.getCell(0) == null || (row.getCell(0).getDateCellValue()).equals("") || StringUtils.isEmpty(row.getCell(12).getStringCellValue())) {
                    break;//如果日期或员工ID为空则判断以下是空行无数据，停止写入
                }
                //读取数据加入对象
                String[] type = {"Date", "Double", "Double", "Double", "String", "Double", "Double", "Double", "Double", "Double", "Double", "String", "String", "String"};
                Object[] data = ExcelUtil.getExcelData(row, type);
                cDailySpending.setDsDate((Date) data[0]);
                cDailySpending.setDsTrafficTaxi((Double) data[1]);
                cDailySpending.setDsTrafficOther((Double) data[2]);
                cDailySpending.setDsTrafficTotal((Double) data[1] + (Double) data[2]);
                cDailySpending.setDsStayTotalprice((Double) data[3]);
                cDailySpending.setDsStayPeople((String) data[4]);
                cDailySpending.setDsStayCount((int) Math.ceil((Double) data[5]));
                cDailySpending.setDsStayUnitprice((Double) data[6]);
                cDailySpending.setDsEntertainStu((Double) data[7]);
                cDailySpending.setDsEntertainLeader((Double) data[8]);
                cDailySpending.setDsServices((Double) data[9]);
                cDailySpending.setDsOther((Double) data[10]);
                cDailySpending.setDsOtherState((String) data[11]);
                User user = userService.findByName((String) data[12]);
                cDailySpending.setDsSellerUid(user.getUid());
                Double total = (Double) data[1] + (Double) data[2] + (Double) data[3] + (Double) data[7] + (Double) data[8] + (Double) data[9] + (Double) data[10];
                cDailySpending.setDsTotal(total);
                cDailySpending.setDsRemark((String) data[13]);
                list.add(cDailySpending);
            }
            if (cDailySpendingService.uploadExcelService(list)) {
                result.setId(0);
                result.setData("上传成功！");
                System.out.println("shangchua上传成功！");
            } else {
                result.setId(1);
                result.setData("上传失败哦，请重试！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            result.setId(-1);
            result.setData("服务器异常，请联系管理员哦！");
        }
        System.out.println(result);
        return result;
    }

    //格式-日常经费导入模板_表
    private HSSFSheet getRows(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("日常经费表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);

        //第二行的单元格进行合并,语句提示
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 13));
        row = sheet.createRow(1);
        HSSFCell beginCell1 = row.createCell(0);
        beginCell1.setCellValue("请删除样例后按照模板字段和单元格格式提交数据");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell1.setCellStyle(cellStyle);

        HSSFRow headRow = sheet.createRow(2);
        String[] hander = {"发生日期", "交通打的", "交通其他", "住宿费用", "住宿人", "住宿间数", "住宿单价", "学生招待", "领导招待", "劳务报酬", "其它费用", "其它费用说明", "员工", "备注"};
        headRow = ExcelUtil.setExcelHander(headRow, hander);
        headRow.setHeightInPoints(25); //设置行的高度
        for (int i = 0; i < 14; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }

        //下拉列表的参数
        List<User> userList = userService.findAllUser();
        for (int i = userList.size() - 1; i >= 0; i--) {
            if (userList.get(i).getAuthority() != 3) {
                userList.remove(i);
            }
        }
        String[] usersArr = new String[userList.size()];
        int index = 0;
        for (User user : userList) {
            usersArr[index] = user.getPersonname();
            index++;
        }
        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, usersArr, 3, 10000, 12, 12);

        //设置所有列列宽
        for (int i = 0; i < 14; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }

    //数据-日常经费导出模板_表
    private HSSFSheet getRowsData(HSSFSheet sheet, HSSFCellStyle cellStyle, HSSFCellStyle cellStyle2) {
        HSSFRow row;// 将第一行的三个单元格给合并,模板固定
        //注意 java的参数是从0开始 即表示excel中的一行或者第一列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 15));
        row = sheet.createRow(0);
        HSSFCell beginCell0 = row.createCell(0);
        beginCell0.setCellValue("日常经费表");
        //cellStyle为水平居中字体加粗单元格样式
        beginCell0.setCellStyle(cellStyle);
        HSSFRow headRow = sheet.createRow(1);
        String[] hander = {"发生日期", "交通打的", "交通其他", "交通总计", "住宿费用", "住宿人", "住宿间数", "住宿单价", "学生招待", "领导招待", "劳务报酬", "其它费用", "其它费用说明", "员工", "总计", "备注"};
        headRow = ExcelUtil.setExcelHander(headRow, hander);
        headRow.setHeightInPoints(25); //设置行的高度
        for (int i = 0; i < 16; i++) {
            headRow.getCell(i).setCellStyle(cellStyle2);
        }

        //下拉列表的参数
        List<User> userList = userService.findAllUser();
        for (int i = userList.size() - 1; i >= 0; i--) {
            if (userList.get(i).getAuthority() != 3) {
                userList.remove(i);
            }
        }
        String[] usersArr = new String[userList.size()];
        int index = 0;
        for (User user : userList) {
            usersArr[index] = user.getPersonname();
            index++;
        }
        //设置当前工作表的什么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
        sheet = ExcelUtil.setHSSFValidation(sheet, usersArr, 3, 10000, 13, 13);

        //设置所有列列宽
        for (int i = 0; i < 16; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        } // 设置列的宽度
        return sheet;
    }

}
